#!/var/bin/env python3
# -*- coding: utf-8 -*-

import smtplib
import logging.config
import sys
import os
import json
import glob
import psycopg2.extras
import time
from datetime import datetime, timedelta, timezone
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders


# 初期化
mail_data_json = None
error_json = None
setting_json = None
database = None
user = None
password = None
host = None
port = None
accessory_file_list = []
mail_id = ""
mail_content = ""
mail_title = ""
accessory = ""

# db table name
batch_manage_db_name = "m_batch_manage"
error_info_table_name = "t_if_error_info"

# logger　インステンスを取得する
logger = logging.getLogger()


# jsonファイルを読み込み
def read_json(json_file_path, csv_table_name=None):
    if not os.path.exists(json_file_path):
        raise OSError("JSONFileNotFoundError")
    with open(json_file_path) as json_fp:
        json_data = json.loads(json_fp.read())
        if csv_table_name is None:
            return json_data
        else:
            if csv_table_name not in json_data.keys():
                raise OSError("JSONKeyNotFoundError")
            return json_data[csv_table_name]


# UTC時間 TO JST時間
def utc_to_jst(datetime):
    try:
        t = datetime.timetuple()
        timestamp = int(time.mktime(t))
        JST = timezone(timedelta(hours=+9), 'JST')
        jst_time = datetime.fromtimestamp(timestamp, JST)
        return jst_time.strftime("%Y-%m-%d %H:%M:%S")
    except ValueError:
        raise ValueError(error_json["time_value_change_error"])


# UTC時間を取得する
def get_utc():
    temp_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    date_time = datetime.strptime(temp_time, '%Y-%m-%d %H:%M:%S')
    ts = int(time.mktime(date_time.timetuple()))
    UTC = timezone(timedelta(hours=+0), 'UTC')
    date_timeWith_zone = datetime.fromtimestamp(ts, UTC)
    return date_timeWith_zone


# 読み込む初期化
def init(mail_id_num):
    conn = None
    cur = None
    try:
        global mail_data_json, error_json, setting_json, database, user, password, host, port, mail_id, mail_content, mail_title, accessory
        mail_id = mail_id_num
        # メール用データを読み込む
        mail_data_json = read_json("./mail.json", mail_id_num)
        accessory = mail_data_json["accessory"]
        # 設定ファイルを読み込む
        setting_json = read_json("./setting.ini")
        # DBに関する変数定義
        database = setting_json["database"]
        user = setting_json["user"]
        password = setting_json["password"]
        host = setting_json["host"]
        port = setting_json["port"]
        # DB接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # ログ出力設定を行う
        log_file_path = setting_json["log_file_path"] + mail_data_json["batch_id"]
        if not os.path.exists(log_file_path):
            os.makedirs(log_file_path)
        logging.config.fileConfig('./logging.conf',
                                  defaults={'logdir': log_file_path, 'batch_name': mail_data_json["batch_id"]})
        # idによって　情報を取得する
        if mail_id == "GSMLBA10GSMLBA11":
            is_error = False
            tmp_mail_content = ""
            file_name_list_kbn = mail_data_json["csv_file_name"]
            for kbn in file_name_list_kbn.keys():
                file_name_list = mail_data_json["csv_file_name"][kbn]["file_name"]
                for index in range(len(file_name_list)):
                    # indexによって、json_dataを生成する
                    json_data = read_json("./data.json", file_name_list[index])
                    # error_dataを取得する
                    error_msg_sql = """SELECT COUNT(1) FROM %s WHERE batch_id = %%s;"""
                    cur.execute(error_msg_sql % error_info_table_name, [json_data["batchId"]])
                    error_count_result = cur.fetchone()
                    # error dataがあるかどうか判断する
                    if error_count_result[0] != 0:
                        is_error = True
                        accessory = "True"
                        break
                # error dataがあるので、error正文を生成する
                if is_error:
                    mail_title = mail_data_json["error_mail_title"]
                    tmp_mail_content = mail_data_json["csv_file_name"][kbn]["error_text_content"]
                else:
                    # error dataがないので、正常正文を生成する
                    mail_title = mail_data_json["mail_title"]
                    tmp_mail_content = mail_data_json["csv_file_name"][kbn]["text_content"]
                mail_content += tmp_mail_content
        elif mail_id == "GSMLBA06GSMLBA07":
            mail_content_str = ""
            # json定義を読み込む
            json_data = read_json("./data.json", mail_data_json["csv_file_name"][0])
            # error_dataを取得する
            error_msg_sql = """SELECT key_info, error_info FROM %s WHERE batch_id = %%s;"""
            cur.execute(error_msg_sql % error_info_table_name, [json_data["batchId"]])
            # もしエラーデータがあれば、エラー情報を取得する
            if cur.rowcount != 0:
                mail_title = mail_data_json["error_mail_title"]
                content = mail_data_json["error_content"]
                results = cur.fetchall()
                for row in results:
                    key_info = row["key_info"]
                    temp_key_info_list = key_info.split(",")
                    syohin_cd = temp_key_info_list[0]
                    lot_number = temp_key_info_list[2]
                    mail_content_str += "\t商品コード：%s　\tロットNo：%s \n" % (syohin_cd, lot_number)
                mail_content = content.format(info=mail_content_str)
            else:
                # もしエラーデータがなければ、正しい情報を取得する
                mail_title = mail_data_json["mail_title"]
                mail_content = mail_data_json["content"]
        elif mail_id == "GSMLBA12GSMLBA13":
            is_error = False
            tmp_mail_content = ""
            file_name_list_kbn = mail_data_json["csv_file_name"]
            for kbn in file_name_list_kbn.keys():
                file_name_list = mail_data_json["csv_file_name"][kbn]["file_name"]
                for index in range(len(file_name_list)):
                    # indexによって、json_dataを生成する
                    json_data = read_json("./data.json", file_name_list[index])
                    # error_dataを取得する
                    error_msg_sql = """SELECT COUNT(1) FROM %s WHERE batch_id = %%s;"""
                    cur.execute(error_msg_sql % error_info_table_name, [json_data["batchId"]])
                    error_count_result = cur.fetchone()
                    # error dataがあるかどうか判断する
                    if error_count_result[0] != 0:
                        is_error = True
                        accessory = "True"
                        break
                # error dataがあるので、error正文を生成する
                if is_error:
                    mail_title = mail_data_json["error_mail_title"]
                    tmp_mail_content = mail_data_json["csv_file_name"][kbn]["error_text_content"]
                else:
                    # error dataがないので、正常正文を生成する
                    mail_title = mail_data_json["mail_title"]
                    tmp_mail_content = mail_data_json["csv_file_name"][kbn]["text_content"]
                mail_content += tmp_mail_content
        else:
            mail_content = mail_data_json["content"]
            mail_title = mail_data_json["mail_title"]
        # エラーメッセージを読み込む
        error_json = read_json("./error.json")
        return True
    except BaseException as bep:
        # 設定ファイルを読み込む時になにかエラーが発生したら、エラーログを出力して、システム終了
        logger.error(bep, exc_info=1)
        return False
    finally:
        if conn:
            cur.close()
            conn.close()


# 送信メール実行情報をバッチテーブルを入ります。
def insert_batch_info():
    conn = None
    cur = None
    try:
        # UTC時間を取得する
        utc_time = get_utc()
        # DB接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # m_batch_manageから検索する
        select_sql = """ SELECT COUNT(1) FROM %s WHERE batch_id=%%s """
        cur.execute(select_sql % batch_manage_db_name, [mail_data_json["batch_id"]])
        select_count_result = cur.fetchone()
        # resultによって、操作を区分する
        if select_count_result[0] != 0:
            # update 操作
            update_sql = """ UPDATE %s SET status = %%s, system_kbn = %%s, first_execution_datetime = %%s, update_user = %%s, update_datetime = %%s WHERE batch_id = %%s """
            cur.execute(update_sql % batch_manage_db_name,
                        ['1', mail_data_json["system_kbn"], utc_time, mail_data_json["batch_id"], utc_time, mail_data_json["batch_id"]])
        else:
            # insert 操作
            insert_sql = """ INSERT INTO %s (batch_id, status, system_kbn, first_execution_datetime, regist_user, regist_datetime) VALUES (%%s, %%s, %%s, %%s, %%s, %%s)"""
            cur.execute(insert_sql % batch_manage_db_name,
                        [mail_data_json["batch_id"], '1', mail_data_json["system_kbn"], utc_time, mail_data_json["batch_id"], utc_time])
        conn.commit()
    except BaseException as bep:
        logger.error(bep, exc_info=1)
        sys.exit(1)
    finally:
        if conn:
            cur.close()
            conn.close()


# get factory_id
def get_factory_id(cur,sql_list, temp_list, temp_str):
    #　すべての工場IDを取得する
    for select_sql in sql_list:
        cur.execute(select_sql)
        record_count_result = cur.fetchall()
        if cur.rowcount != 0:
            for index in range(len(record_count_result)):
                temp_result = record_count_result[index][0]
                if temp_result is None:
                    temp_result = "工場識別不明"
                if temp_result not in temp_list:
                    temp_list.append(temp_result)
    # 工場IDによって、レコード数を取得する
    for factory_id in temp_list:
        temp_data = [factory_id]
        count_sql_list = [
            """ SELECT COUNT(1) FROM %s WHERE factory_id = %%s;""",
            """SELECT COUNT(1) FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON 
                             T_PRODUCT_CERTIFICATE.syohin_tokutei_cd = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_tokutei_cd  and 
                             T_PRODUCT_CERTIFICATE.syohin_meisai_no = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_meisai_no and 
                             T_PRODUCT_CERTIFICATE.lot_number = TMP_T_PRODUCT_CERTIFICATE_VAL.lot_number and
                             T_PRODUCT_CERTIFICATE.je_attribute = TMP_T_PRODUCT_CERTIFICATE_VAL.je_kbn
                             WHERE TMP_T_PRODUCT_CERTIFICATE_VAL.syori_flg = '1' and factory_id = %%s;""",
            """ SELECT COUNT(1) FROM 
                            (SELECT TMP_M_PRODUCT_LOT_MARK.lot_mark, lot_number FROM %s INNER JOIN M_PRODUCT_LOT_NO ON TMP_M_PRODUCT_LOT_MARK.lot_mark = M_PRODUCT_LOT_NO.lot_mark WHERE TMP_M_PRODUCT_LOT_MARK.syori_flg = '1') as mark_table
                            INNER JOIN T_PRODUCT_CERTIFICATE ON mark_table.lot_number = T_PRODUCT_CERTIFICATE.lot_number WHERE factory_id = %%s; """,
            """ SELECT COUNT(1) FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON T_PRODUCT_CERTIFICATE.lot_number = TMP_M_PRODUCT_LOT_NO.lot_number WHERE factory_id = %%s and TMP_M_PRODUCT_LOT_NO.syori_flg = '1'; """
        ]
        count_sql_list_isnull = [
            """ SELECT COUNT(1) FROM %s WHERE factory_id IS NULL;""",
            """SELECT COUNT(1) FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON 
                             T_PRODUCT_CERTIFICATE.syohin_tokutei_cd = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_tokutei_cd  and 
                             T_PRODUCT_CERTIFICATE.syohin_meisai_no = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_meisai_no and 
                             T_PRODUCT_CERTIFICATE.lot_number = TMP_T_PRODUCT_CERTIFICATE_VAL.lot_number and
                             T_PRODUCT_CERTIFICATE.je_attribute = TMP_T_PRODUCT_CERTIFICATE_VAL.je_kbn
                             WHERE TMP_T_PRODUCT_CERTIFICATE_VAL.syori_flg = '1' and factory_id IS NULL;""",
            """ SELECT COUNT(1) FROM 
                            (SELECT TMP_M_PRODUCT_LOT_MARK.lot_mark, lot_number FROM %s INNER JOIN M_PRODUCT_LOT_NO ON TMP_M_PRODUCT_LOT_MARK.lot_mark = M_PRODUCT_LOT_NO.lot_mark WHERE TMP_M_PRODUCT_LOT_MARK.syori_flg = '1') as mark_table
                            INNER JOIN T_PRODUCT_CERTIFICATE ON mark_table.lot_number = T_PRODUCT_CERTIFICATE.lot_number WHERE factory_id IS NULL; """,
            """ SELECT COUNT(1) FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON T_PRODUCT_CERTIFICATE.lot_number = TMP_M_PRODUCT_LOT_NO.lot_number WHERE factory_id IS NULL and TMP_M_PRODUCT_LOT_NO.syori_flg = '1'; """
        ]
        table_name_list = [
            "TMP_T_PRODUCT_CERTIFICATE",
            "TMP_T_PRODUCT_CERTIFICATE_VAL",
            "TMP_M_PRODUCT_LOT_MARK",
            "TMP_M_PRODUCT_LOT_NO"
        ]
        for sql_index in range(len(count_sql_list)):
            if factory_id == '工場識別不明':
                cur.execute(count_sql_list_isnull[sql_index] % table_name_list[sql_index])
            else:
                cur.execute(count_sql_list[sql_index] % table_name_list[sql_index],[factory_id])
            sql_cert_count = cur.fetchone()
            temp_data.append(sql_cert_count[0])
        temp_str += mail_content % tuple(temp_data)
    return temp_str


# create mail content
def create_mail_content(sys_kbn_first_execution_min_time_jst, sys_kbn_last_execution_max_time_jst):
    conn = None
    cur = None
    try:
        # DB接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        if mail_id == "GSMLBA10GSMLBA11":
            accessory_file_json = mail_data_json["csv_file_name"]
            kbn_keys = accessory_file_json.keys()
            temp_list = []
            for key in kbn_keys:
                file_names = accessory_file_json[key]["file_name"]
                temp_list.append(key)
                temp_list.append(sys_kbn_first_execution_min_time_jst)
                for file_name in file_names:
                    json_data = read_json("./data.json", file_name)
                    select_sql = """ SELECT COUNT(1) FROM %s WHERE syori_flg='1' """
                    cur.execute(select_sql % json_data["tptableName"])
                    record_count_result = cur.fetchone()
                    record_count = record_count_result[0]
                    temp_list.append(record_count)
                temp_list.append(sys_kbn_last_execution_max_time_jst)
            mail_content_str = mail_content % tuple(temp_list)
        elif mail_id == "GSMLBA12GSMLBA13":
            temp_str = ""
            temp_list = []
            sql_list = [
                """SELECT factory_id FROM TMP_T_PRODUCT_CERTIFICATE WHERE syori_flg='1' GROUP BY factory_id;""",
                """SELECT factory_id FROM TMP_T_PRODUCT_CERTIFICATE_VAL INNER JOIN T_PRODUCT_CERTIFICATE ON
                                                    T_PRODUCT_CERTIFICATE.syohin_tokutei_cd = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_tokutei_cd  and
                                                    T_PRODUCT_CERTIFICATE.syohin_meisai_no = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_meisai_no and
                                                    T_PRODUCT_CERTIFICATE.lot_number = TMP_T_PRODUCT_CERTIFICATE_VAL.lot_number and
                                                    T_PRODUCT_CERTIFICATE.je_attribute = TMP_T_PRODUCT_CERTIFICATE_VAL.je_kbn
                                                    WHERE TMP_T_PRODUCT_CERTIFICATE_VAL.syori_flg = '1';""",
                """SELECT factory_id FROM 
        (SELECT TMP_M_PRODUCT_LOT_MARK.lot_mark, lot_number FROM TMP_M_PRODUCT_LOT_MARK INNER JOIN M_PRODUCT_LOT_NO ON TMP_M_PRODUCT_LOT_MARK.lot_mark = M_PRODUCT_LOT_NO.lot_mark WHERE TMP_M_PRODUCT_LOT_MARK.syori_flg = '1') as mark_table
        INNER JOIN T_PRODUCT_CERTIFICATE ON mark_table.lot_number = T_PRODUCT_CERTIFICATE.lot_number;""",
                """ SELECT factory_id FROM TMP_M_PRODUCT_LOT_NO INNER JOIN T_PRODUCT_CERTIFICATE ON T_PRODUCT_CERTIFICATE.lot_number = TMP_M_PRODUCT_LOT_NO.lot_number WHERE TMP_M_PRODUCT_LOT_NO.syori_flg = '1' """
            ]
            temp_str = get_factory_id(cur, sql_list, temp_list, temp_str)
            mail_content_str = mail_data_json["csv_file_name"]["[検査成績書基本情報]"]["content_title"] + temp_str
            mail_content_str = mail_content_str % (
                sys_kbn_first_execution_min_time_jst, sys_kbn_last_execution_max_time_jst)
        else:
            mail_content_str = mail_content % (
                sys_kbn_first_execution_min_time_jst, sys_kbn_last_execution_max_time_jst)
        return  mail_content_str
    except BaseException as bep:
        logger.error(bep, exc_info=1)
        return None
    finally:
        if conn:
            cur.close()
            conn.close()


# メール送信制御条件をチェックする
def check_send_mail_condition():
    # DB連携変数定義
    conn = None
    cur = None
    logger.info("メール送信制御条件チェック処理が開始します。")
    # 実行start time
    check_start_time = time.time()
    try:
        # DB接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        select_sql = """SELECT MIN(first_execution_datetime) as first_min_datetime, 
                               MAX(last_execution_datetime) as last_max_datetime, 
                               MIN(last_execution_datetime) as last_min_datetime 
                               FROM %s WHERE "status" = '0' AND "system_kbn" = %%s AND "batch_id" != %%s;"""
        cur.execute(select_sql % batch_manage_db_name, [mail_data_json["system_kbn"], mail_data_json["batch_id"]])
        result = cur.fetchone()
        if result["first_min_datetime"] is not None or result["last_max_datetime"] is not None:
            # 結果を取得する
            sys_kbn_last_execution_min_time_utc = result["last_min_datetime"]
            sys_kbn_last_execution_max_time_utc = result["last_max_datetime"]
            sys_kbn_first_execution_min_time_utc = result["first_min_datetime"]
            # UTC時間 TO JST時間
            sys_kbn_last_execution_min_time_jst = utc_to_jst(sys_kbn_last_execution_min_time_utc)
            sys_kbn_last_execution_max_time_jst = utc_to_jst(sys_kbn_last_execution_max_time_utc)
            sys_kbn_first_execution_min_time_jst = utc_to_jst(sys_kbn_first_execution_min_time_utc)

            select_sql = """SELECT last_execution_datetime FROM %s WHERE "status" = '0' and "batch_id" = %%s;"""
            cur.execute(select_sql % "m_batch_manage", [mail_data_json["batch_id"]])
            # 実行したことがあるので、条件を比較する
            if cur.rowcount != 0:
                # 結果を取得する
                send_mail_last_execution_time_result = cur.fetchone()
                send_mail_last_execution_time_utc = send_mail_last_execution_time_result["last_execution_datetime"]
                # UTC時間 TO JST時間
                send_mail_last_execution_time_jst = utc_to_jst(send_mail_last_execution_time_utc)
                # 時間を比較してる
                if send_mail_last_execution_time_jst < sys_kbn_last_execution_min_time_jst:
                    insert_batch_info()
                    return sys_kbn_first_execution_min_time_jst, sys_kbn_last_execution_max_time_jst
                else:
                    logger.info(error_json["send_mail_conditions_check_error"])
                    return None
            # 初めて実行する時に
            else:
                insert_batch_info()
                return sys_kbn_first_execution_min_time_jst, sys_kbn_last_execution_max_time_jst
        else:
            # データが無いというのは、CSVファイルをまだ読み込まないです。
            logger.info(error_json["data_not_found_error"])
            return None
    except BaseException as bep:
        logger.error("%s : " + error_json["database_error"], bep, exc_info=1)
        return None
    finally:
        if conn:
            cur.close()
            conn.close()
        # 実行end time
        check_end_time = time.time()
        elapsed_time = check_end_time - check_start_time
        logger.info("メール送信制御条件チェック処理が終了しました。処理時間：" + str(round(elapsed_time, 2)) + "秒")


# メール送信
def send_mail(mail_content_str):
    conn = None
    cur = None
    logger.info("メール送信処理が開始します。")
    # 実行start time
    check_start_time = time.time()
    # sql文を生成する
    update_sql = """  UPDATE %s SET status = %%s, last_execution_datetime = %%s, update_user = %%s, 
                                    update_datetime = %%s, del_flg = '0' WHERE batch_id = %%s  """
    temp_list = []
    utc_time = get_utc()
    try:
        # 送信インステンスを生成する
        message = MIMEMultipart()
        # 添付送信する時処理
        if accessory == "True":
            # 添付ファイルを生成する
            create_accessory_file()
            for accessory_file_name in accessory_file_list:
                with open(accessory_file_name, 'rb') as f:
                    mime = MIMEBase('text', 'txt', filename=accessory_file_name)
                    mime.add_header('Content-Disposition', 'attachment',
                                    filename=('Shift-JIS', '', accessory_file_name))
                    mime.set_payload(f.read())
                    encoders.encode_base64(mime)
                    message.attach(mime)
        # 送信メール設定
        smtp_server = setting_json["smtp_server"]
        mail_server_port = setting_json["mail_server_port"]
        from_mail = setting_json["from_mail_address"]
        mail_pass = setting_json["mail_pass"]
        to_mail = mail_data_json["to_mail_address"]
        cc_mail = mail_data_json["cc_mail_address"]
        bcc_mail = mail_data_json["bcc_mail_address"]

        message['From'] = from_mail
        message['To'] = ",".join(to_mail)
        message['Cc'] = ",".join(cc_mail)
        message['Bcc'] = ",".join(bcc_mail)
        message['Subject'] = mail_title
        message.attach(MIMEText(mail_content_str, 'plain', 'utf-8'))

        # メールを送信する
        s = smtplib.SMTP_SSL(smtp_server, mail_server_port)
        s.login(from_mail, mail_pass)
        s.sendmail(from_mail, to_mail + cc_mail + bcc_mail, message.as_string())
        s.quit()
        # dbに接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # メール送信後に、bacth情報をdbに入る
        temp_list += ['0', utc_time, mail_data_json["batch_id"], utc_time, mail_data_json["batch_id"]]
        cur.execute(update_sql % batch_manage_db_name, temp_list)
        # メール送信後に、エラー情報を削除する
        select_sql = """DELETE FROM %s WHERE batch_group_id = %%s"""
        cur.execute(select_sql % error_info_table_name, [mail_data_json["system_kbn"]])
    except BaseException as bep:
        # メール送信エラーが出る場合、dbを更新する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        temp_list += ['9', utc_time, mail_data_json["batch_id"], utc_time, mail_data_json["batch_id"]]
        cur.execute(update_sql % batch_manage_db_name, temp_list)
        # エラー情報を出力する
        logger.error("%s : " + error_json["send_mail_error"], bep, exc_info=1)
        sys.exit(1)
    finally:
        if conn:
            conn.commit()
            cur.close()
            conn.close()
        # を削除する
        files_path = glob.glob("./*.txt")
        for file_path in files_path:
            os.remove(file_path)
        # 実行end time
        check_end_time = time.time()
        elapsed_time = check_end_time - check_start_time
        logger.info("メール送信処理が終了しました。処理時間：" + str(round(elapsed_time, 2)) + "秒")


# 添付ファイル内容を生成する
def create_accessory_file():
    # DB連携変数定義
    conn = None
    cur = None
    logger.info("添付ファイル内容生成処理が開始します。")
    # 実行start time
    check_start_time = time.time()
    first_write_title = True
    error_first_write_title = True
    try:
        # DB接続する
        conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        accessory_file_json = mail_data_json["csv_file_name"]
        kbn_keys = accessory_file_json.keys()
        # CSV処理区分runloop
        for item_key in kbn_keys:
            first_write_kbn = True
            error_first_write_kbn = True
            kbn_item_json = accessory_file_json[item_key]
            file_names = kbn_item_json["file_name"]
            for file_item_name in file_names:
                error_first_write_table_name = True
                error_data_kbn = True
                # json定義を読み込む
                json_data = read_json("./data.json", file_item_name)
                # error count
                error_count_sql = """SELECT COUNT(1) FROM %s WHERE batch_id = %%s;"""
                cur.execute(error_count_sql % error_info_table_name, [json_data["batchId"]])
                error_count_result = cur.fetchone()
                if error_count_result[0] != 0:
                    # もし米国、ドイツの場合、正しい内容を出力する
                    if mail_id == "GSMLBA08" or mail_id == "GSMLBA09":
                        db_name = json_data["tptableName"]
                        accessory_file_list.append(mail_data_json["accessory_file_name"])
                        select_sql = """SELECT COUNT(1) FROM %s WHERE syori_flg = '1' AND region_cd = %%s;"""
                        cur.execute(select_sql % db_name, [mail_data_json["region_cd"]])
                        record_count_result = cur.fetchone()
                        record_count = record_count_result[0]
                        if record_count > 0:
                            text_content = kbn_item_json["text_content"] % record_count
                            select_sql = """SELECT syohin_tokutei_cd, syohin_meisai_no, lot_number FROM %s WHERE syori_flg = '1' AND region_cd = %%s;"""
                            cur.execute(select_sql % db_name, [mail_data_json["region_cd"]])
                            if cur.rowcount != 0:
                                info = ""
                                while True:
                                    results = cur.fetchmany(setting_json["fetchsize"])
                                    if results == []:
                                        break
                                    for row_data in results:
                                        syohin_tokutei_cd = row_data["syohin_tokutei_cd"]
                                        syohin_meisai_no = row_data["syohin_meisai_no"]
                                        lot_number = row_data["lot_number"]
                                        info += syohin_tokutei_cd + ", " + str(
                                            syohin_meisai_no) + ", " + lot_number + "\n"
                                with open("./" + mail_data_json["accessory_file_name"], 'a+',
                                          encoding="Shift-JIS") as out:
                                    out.write(mail_data_json["accessory_file_title"]["title"] + '\n')
                                    out.write(text_content.format(info=info))
                    # エラーデータがある場合、エラー添付に出力
                    error_msg_sql = """SELECT key_info, error_info FROM %s WHERE batch_id = %%s;"""
                    cur.execute(error_msg_sql % error_info_table_name, [json_data["batchId"]])
                    if cur.rowcount != 0:
                        # accessory_file_listを生成する
                        accessory_file_list.append(mail_data_json["accessory_file_name_error"])
                        # データがあれば、添付ファイルにタイトルを書きこみます
                        if error_first_write_title is True:
                            with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write(mail_data_json["accessory_file_title"]["error_title"] + '\n\n')
                            error_first_write_title = False
                        if error_first_write_kbn is True and mail_id != "GSMLBA10GSMLBA11" and mail_id != "GSMLBA12GSMLBA13":
                            with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write(item_key + '\n')
                            error_first_write_kbn = False
                        if mail_id == "GSMLBA10GSMLBA11":
                            with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write(mail_data_json["error_data_kbn"][json_data["batchId"]] + '\n')
                            error_data_kbn = False
                        if error_first_write_table_name is True and mail_id != "GSMLBA12GSMLBA13" \
                                and mail_id != "GSMLBA10GSMLBA11" and mail_id != "GSMLBA08" and mail_id != "GSMLBA09":
                            with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write('\t' + json_data["tableName"] + '\n')
                            error_first_write_table_name = False
                        data_dic = {}
                        while True:
                            results = cur.fetchmany(setting_json["fetchsize"])
                            if results == []:
                                break
                            for row_data in results:
                                key_info = row_data["key_info"]
                                error_info = row_data["error_info"]
                                with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                          encoding="Shift-JIS") as out:
                                    if mail_id == "GSMLBA10GSMLBA11":
                                        out.write(kbn_item_json["accessory_file_error_content"] % (key_info, error_info))
                                    elif mail_id == "GSMLBA12GSMLBA13":
                                        select_sql_certificate = """ SELECT factory_id FROM %s WHERE syohin_tokutei_cd = %%s and 
                                                          syohin_meisai_no = %%s and lot_number = %%s and je_attribute = %%s """
                                        temp_key_list = key_info.split(',')
                                        cur.execute(select_sql_certificate % "TMP_T_PRODUCT_CERTIFICATE", tuple(temp_key_list))
                                        rest = cur.fetchone()
                                        if cur.rowcount != 0:
                                            data_list = []
                                            factory_id = rest["factory_id"]

                                            if factory_id is None:
                                                factory_id = "工場識別不明"
                                            if factory_id in data_dic.keys():
                                                temp_list = data_dic[factory_id]
                                                temp_list.append("[検査成績書基本情報]" + "|" + key_info + "|" +error_info)
                                                data_dic[factory_id] = temp_list
                                            else:
                                                data_dic[factory_id] = data_list
                                                data_list.append("[検査成績書基本情報]" + "|" + key_info + "|" +error_info)
                                    else:
                                        out.write("\t" + kbn_item_json["error_text_content"] % (key_info, error_info))
                        if mail_id == "GSMLBA12GSMLBA13":
                            # 工場区分を取得する
                            select_sql_error_val = """ SELECT key_info, error_info FROM %s WHERE batch_id = %%s"""
                            cur.execute(select_sql_error_val % error_info_table_name, ["GSBAQLGS0220"])
                            if cur.rowcount != 0:
                                val_error_res = cur.fetchall()
                                for item in val_error_res:
                                    val_key_info = item["key_info"]
                                    val_error_info = item["error_info"]

                                    select_sql_val = """ SELECT factory_id FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON 
                                                                        T_PRODUCT_CERTIFICATE.syohin_tokutei_cd = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_tokutei_cd  and 
                                                                        T_PRODUCT_CERTIFICATE.syohin_meisai_no = TMP_T_PRODUCT_CERTIFICATE_VAL.syohin_meisai_no and 
                                                                        T_PRODUCT_CERTIFICATE.lot_number = TMP_T_PRODUCT_CERTIFICATE_VAL.lot_number and
                                                                        T_PRODUCT_CERTIFICATE.je_attribute = TMP_T_PRODUCT_CERTIFICATE_VAL.je_kbn
                                                                        WHERE TMP_T_PRODUCT_CERTIFICATE_VAL.syori_flg = '1'  and TMP_T_PRODUCT_CERTIFICATE_VAL.SYOHIN_TOKUTEI_CD = %%s and 
                                                                        TMP_T_PRODUCT_CERTIFICATE_VAL.SYOHIN_MEISAI_NO = %%s and TMP_T_PRODUCT_CERTIFICATE_VAL.LOT_NUMBER = %%s and 
                                                                        TMP_T_PRODUCT_CERTIFICATE_VAL.JE_KBN = %%s and TEST_ITEM_CODE = %%s"""
                                    cur.execute(select_sql_val % "TMP_T_PRODUCT_CERTIFICATE_VAL",
                                                val_key_info.split(","))
                                    val_res = cur.fetchall()
                                    if val_res != 0:
                                        for index_item in val_res:
                                            factory_id = index_item[0]
                                            if factory_id is None:
                                                factory_id = "工場識別不明"
                                            if factory_id in data_dic.keys():
                                                temp_list = data_dic[factory_id]
                                                temp_list.append("[検査成績書検査成績情報テーブル]" + "|" + val_key_info + "|" + val_error_info)
                                                data_dic[factory_id] = temp_list
                                            else:
                                                data_dic[factory_id] = data_list
                                                data_list.append("[検査成績書検査成績情報テーブル]" + "|" + val_key_info + "|" + val_error_info)
                            select_error_sql_lot_mark = """  SELECT key_info, error_info FROM %s WHERE batch_id = %%s """
                            cur.execute(select_error_sql_lot_mark % error_info_table_name, ["GSBAQLGS0320"])
                            if cur.rowcount != 0:
                                lot_mark_error_res = cur.fetchall()
                                for item in lot_mark_error_res:
                                    lot_mark_key_info = item["key_info"]
                                    lot_mark_error_info = item["error_info"]
                                    select_sql_lot_mark = """  SELECT factory_id FROM 
                            (SELECT TMP_M_PRODUCT_LOT_MARK.lot_mark, lot_number FROM %s INNER JOIN M_PRODUCT_LOT_NO ON TMP_M_PRODUCT_LOT_MARK.lot_mark = M_PRODUCT_LOT_NO.lot_mark WHERE TMP_M_PRODUCT_LOT_MARK.syori_flg = '1') as mark_table
                            INNER JOIN T_PRODUCT_CERTIFICATE ON mark_table.lot_number = T_PRODUCT_CERTIFICATE.lot_number WHERE mark_table.lot_mark = %%s"""
                                    cur.execute(select_sql_lot_mark % "TMP_M_PRODUCT_LOT_MARK", [lot_mark_key_info])
                                    lot_mark_res = cur.fetchall()
                                    if lot_mark_res != 0:
                                        for index_item in lot_mark_res:
                                            factory_id = index_item[0]
                                            if factory_id is None:
                                                factory_id = "工場識別不明"
                                            if factory_id in data_dic.keys():
                                                temp_list = data_dic[factory_id]
                                                temp_list.append("[検査成績書Lot記号マスタ]" + "|" + lot_mark_key_info + "|" + lot_mark_error_info)
                                                data_dic[factory_id] = temp_list
                                            else:
                                                data_dic[factory_id] = data_list
                                                data_list.append("[検査成績書Lot記号マスタ]" + "|" + lot_mark_key_info + "|" + lot_mark_error_info)
                            select_error_sql_lot_num = """  SELECT key_info, error_info FROM %s WHERE batch_id = %%s """
                            cur.execute(select_error_sql_lot_num % error_info_table_name, ["GSBAQLGS0420"])
                            if cur.rowcount != 0:
                                lot_no_error_res = cur.fetchall()
                                for item in lot_no_error_res:
                                    lot_no_key_info = item["key_info"]
                                    lot_no_error_info = item["error_info"]
                                    select_sql_no = """ SELECT factory_id FROM %s INNER JOIN T_PRODUCT_CERTIFICATE ON T_PRODUCT_CERTIFICATE.lot_number = TMP_M_PRODUCT_LOT_NO.lot_number 
                                                                                          WHERE TMP_M_PRODUCT_LOT_NO.lot_number = %%s and TMP_M_PRODUCT_LOT_NO.syori_flg = '1';"""
                                    cur.execute(select_sql_no % "TMP_M_PRODUCT_LOT_NO", [lot_no_key_info])
                                    lot_no_res = cur.fetchall()
                                    if lot_no_res != 0:
                                        for index_item in lot_no_res:
                                            factory_id = index_item[0]
                                            if factory_id is None:
                                                factory_id = "工場識別不明"
                                            if factory_id in data_dic.keys():
                                                temp_list = data_dic[factory_id]
                                                temp_list.append("[検査成績書Lot番号マスタ]" + "|" + lot_no_key_info + "|" + lot_no_error_info)
                                                data_dic[factory_id] = temp_list
                                            else:
                                                data_dic[factory_id] = data_list
                                                data_list.append("[検査成績書Lot番号マスタ]" + "|" + lot_no_key_info + "|" + lot_no_error_info)
                            # 出力data_dic内容
                            for key in data_dic.keys():
                                title_output = []
                                with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                  encoding="Shift-JIS") as out:
                                    out.write("<工場識別：%s> \n\n" % key)
                                for value in data_dic[key]:
                                    value_list = value.split("|")
                                    with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                              encoding="Shift-JIS") as out:
                                        if value_list[0] not in title_output:
                                            out.write("\t" + value_list[0] + "\n")
                                            title_output.append(value_list[0])
                                        out.write("\t\t(%s):%s\n" % (value_list[1], value_list[2]))

                                with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                  encoding="Shift-JIS") as out:
                                    out.write("\n")
                        with open("./" + mail_data_json["accessory_file_name_error"], 'a+',
                                  encoding="Shift-JIS") as out:
                            out.write('\n')
                else:
                    if mail_id == "GSMLBA10GSMLBA11" or mail_id == "GSMLBA12GSMLBA13":
                        break
                    # accessory_file_listを生成する
                    accessory_file_list.append(mail_data_json["accessory_file_name"])
                    # エラーデータがない場合、正常添付に出力
                    db_name = json_data["tptableName"]
                    if mail_id == "GSMLBA08" or mail_id == "GSMLBA09":
                        select_sql = """SELECT COUNT(1) FROM %s WHERE syori_flg = '1' AND region_cd = %%s;"""
                        cur.execute(select_sql % db_name, [mail_data_json["region_cd"]])
                    else:
                        select_sql = """SELECT COUNT(1) FROM %s WHERE syori_flg = '1';"""
                        cur.execute(select_sql % db_name)
                    record_count_result = cur.fetchone()
                    record_count = record_count_result[0]
                    if record_count > 0:
                        # データがあれば、添付ファイルにタイトルを書きこみます
                        if first_write_title is True:
                            with open("./" + mail_data_json["accessory_file_name"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write(mail_data_json["accessory_file_title"]["title"] + '\n')
                            first_write_title = False
                        if first_write_kbn is True and not (mail_id == "GSMLBA09" or mail_id == "GSMLBA08"):
                            with open("./" + mail_data_json["accessory_file_name"], 'a+',
                                      encoding="Shift-JIS") as out:
                                out.write('\n' + item_key + '\n')
                            first_write_kbn = False
                            with open("./" + mail_data_json["accessory_file_name"], 'a+', encoding="Shift-JIS") as out:
                                out.write(kbn_item_json["text_content"] % (json_data["tableName"], record_count))
                        else:
                            if mail_id == "GSMLBA01":
                                with open("./" + mail_data_json["accessory_file_name"], 'a+',
                                          encoding="Shift-JIS") as out:
                                    out.write(kbn_item_json["text_content"] % (json_data["tableName"], record_count))
                            else:
                                text_content = kbn_item_json["text_content"] % record_count
                                if mail_id == "GSMLBA08" or mail_id == "GSMLBA09":
                                    select_sql = """SELECT syohin_tokutei_cd, syohin_meisai_no, lot_number FROM %s WHERE syori_flg = '1' AND region_cd = %%s;"""
                                    cur.execute(select_sql % db_name, [mail_data_json["region_cd"]])
                                else:
                                    select_sql = """SELECT syohin_tokutei_cd, syohin_meisai_no, lot_number FROM %s WHERE syori_flg = '1';"""
                                    cur.execute(select_sql % db_name)
                                if cur.rowcount != 0:
                                    info = ""
                                    while True:
                                        results = cur.fetchmany(setting_json["fetchsize"])
                                        if results == []:
                                            break
                                        for row_data in results:
                                            syohin_tokutei_cd = row_data["syohin_tokutei_cd"]
                                            syohin_meisai_no = row_data["syohin_meisai_no"]
                                            lot_number = row_data["lot_number"]
                                            info += syohin_tokutei_cd + ", " + str(syohin_meisai_no) + ", " + lot_number + "\n"
                                    with open("./" + mail_data_json["accessory_file_name"], 'a+',
                                              encoding="Shift-JIS") as out:
                                        out.write(text_content.format(info = info))
    except BaseException as bep:
        logger.error("%s : " + error_json["database_error"], bep, exc_info=1)
        sys.exit(1)
    finally:
        if conn:
            cur.close()
            conn.close()
        # 実行end time
        check_end_time = time.time()
        elapsed_time = check_end_time - check_start_time
        logger.info("添付ファイル内容生成処理が終了しました。処理時間：" + str(round(elapsed_time, 2)) + "秒")



